package com.okq.dao;

import com.okq.dao.base.Impl.BaseDao;
import com.okq.entity.JobChart;
import com.okq.entity.JobProduct;
import com.okq.util.page.Page;
import org.apache.commons.lang.StringUtils;
import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.hibernate.type.StandardBasicTypes;
import org.springframework.stereotype.Repository;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 描述 : 工单产品Dao
 * 作者 : zdl
 * 日期 : 2017/6/29 10:58
 */
@Repository
public class JobProductDao extends BaseDao {

    public void saveJobProduct(JobProduct jobProduct) {
        super.save(jobProduct);
    }

    public void deleteJobProduct(JobProduct jobProduct) {
        super.delete(jobProduct);
    }

    public void updateJobProduct(JobProduct jobProduct) {
        super.update(jobProduct);
    }

    public void saveOrUpdateJobProduct(JobProduct jobProduct) {
        super.saveOrUpdate(jobProduct);
    }

    public JobProduct getJobProductById(String id) {
        return (JobProduct) super.get(JobProduct.class, id);
    }

    public List<JobProduct> listAllJobProduct() {
        String hql = "FROM JobProduct WHERE isDelete = 0";
        return super.find(hql);
    }

    public Page<JobProduct> pageByJobProduct(Page<JobProduct> page, JobProduct jobProduct) {
        StringBuilder hqlSb = new StringBuilder();
        hqlSb.append("FROM JobProduct jp WHERE jp.isDelete = 0 ");
        if (jobProduct != null) {
            Map<String, Object> param = new HashMap<>();
            String jobId = jobProduct.getJobId();
            if (StringUtils.isNotBlank(jobId)) {
                hqlSb.append(" AND jp.jobId = ?1 ");
                param.put("1", jobId);
            }
            String startTime = jobProduct.getStartTime();
            if (StringUtils.isNotBlank(startTime)) {
                hqlSb.append(" AND jp.dealTime >= ?2 ");
                param.put("2", startTime);
            }
            String endTime = jobProduct.getEndTime();
            if (StringUtils.isNotBlank(endTime)) {
                hqlSb.append(" AND jp.dealTime <= ?3 ");
                param.put("3", endTime);
            }
            hqlSb.append(" ORDER BY jp.createTime DESC ");
            return super.findByPage(page, hqlSb.toString(), param);
        }
        return page;
    }

    public List<JobProduct> listJobProductByJid(String jobId) {
        if (StringUtils.isNotBlank(jobId)) {
            String hql = "FROM JobProduct WHERE isDelete = 0 AND jobId = '" + jobId + "' ";
            return super.find(hql);
        }
        return null;
    }

    /**
     * 根据产品ID查询工单产品
     *
     * @param productId 产品Id
     * @return JobProduct
     */
    public List<JobProduct> listJobProductByPid(String productId) {
        if (StringUtils.isNotBlank(productId)) {
            String hql = "FROM JobProduct WHERE isDelete = 0 AND productId = '" + productId + "'";
            return super.find(hql);
        }
        return null;
    }

    /**
     * 按天分组查询每天工单数量以及办结数量、未办结数量和剩余未办结总量
     *
     * @param jobProduct 工单产品
     * @return list
     */
    public List<JobChart> listJobByCondition(JobProduct jobProduct) {
        StringBuilder sqlSb = new StringBuilder();
        sqlSb.append("SELECT t2.days AS days,t3.state AS state,IFNULL(t3.num,0) AS number FROM  ");
        sqlSb.append("(SELECT @cdate \\:= date_add(@cdate,INTERVAL -1 DAY) days FROM  (SELECT @cdate \\:= CURDATE() FROM tb_job_product ) t1 ) t2 ");
        sqlSb.append(" LEFT JOIN ");
        sqlSb.append("(SELECT DATE_FORMAT(jp.dealTime,'%Y-%m-%d') AS dealDay,jp.state,COUNT(1) AS num FROM tb_job_product jp ");
        sqlSb.append("WHERE jp.isDelete = 0 AND jp.state <> 3 ");
        String startTime = "";
        if (jobProduct != null) {
            startTime = jobProduct.getStartTime();
            if (StringUtils.isNotBlank(startTime)) {
                sqlSb.append(" AND jp.dealTime >= '").append(startTime).append("' ");
            }
            String endTime = jobProduct.getEndTime();
            if (StringUtils.isNotBlank(endTime)) {
                sqlSb.append(" AND jp.dealTime <= '").append(endTime).append("' ");
            }
            String comCode = jobProduct.getComCode();
            if (StringUtils.isNotBlank(comCode)) {
                sqlSb.append(" AND jp.comCode = '").append(comCode).append("' ");
            }
        }
        sqlSb.append("GROUP BY dealDay,state ORDER BY dealDay ASC) t3 ON t2.days = t3.dealDay WHERE ");
        if (StringUtils.isNotBlank(startTime)) {
            sqlSb.append("t2.days >= DATE_FORMAT('").append(startTime).append("','%Y-%m-%d')");
        } else {
            sqlSb.append("t2.days >= (SELECT DATE_FORMAT(MIN(dealTime),'%Y-%m-%d') FROM tb_job_product where isDelete=0)");
        }
        SQLQuery sqlQuery = super.getSession().createSQLQuery(sqlSb.toString());
        return sqlQuery.addScalar("days", StandardBasicTypes.STRING).addScalar("state", StandardBasicTypes.INTEGER)
                .addScalar("number", StandardBasicTypes.INTEGER).setResultTransformer(Transformers.aliasToBean(JobChart.class)).list();
    }
}
